DECLARE @SelectedAreaList TABLE (AreaSK int, [AreaPath] nvarchar(4000) null);
INSERT @SelectedAreaList 
	SELECT AreaSK, [AreaPath]
	  FROM DimArea
	 WHERE ProjectGUID = @ProjectGuid
	   AND '*' In (@AreaParam) OR CONVERT(NVARCHAR, AreaSK) In (@AreaParam)
;

DECLARE @ChildAreas TABLE (AreaSK int, [AreaPath] nvarchar(4000) null);
INSERT @ChildAreas 
	SELECT DISTINCT a.AreaSK, a.[AreaPath]
	  FROM DimArea a
	 INNER JOIN @SelectedAreaList sa ON a.[AreaPath] LIKE sa.[AreaPath] + '\%';
;

DECLARE @AreasAndChildren TABLE (AreaSK int, [AreaPath]  nvarchar(4000) null);
INSERT @AreasAndChildren 

	SELECT AreaSK, [AreaPath] FROM @SelectedAreaList
	UNION
	SELECT AreaSK, [AreaPath] FROM @ChildAreas
;

DECLARE @SelectedIterationList TABLE (IterationSk int, [IterationPath] nvarchar(4000) null);
INSERT @SelectedIterationList
	SELECT IterationSK, [IterationPath] 
	FROM DimIteration
	WHERE ProjectGUID = @ProjectGuid
	  AND '*' IN (@IterationParam) OR CONVERT(NVARCHAR, IterationSk) IN (@IterationParam)
;

DECLARE @ChildIterations TABLE (IterationSk int, [IterationPath] nvarchar(4000) null);
INSERT @ChildIterations
	SELECT DISTINCT i.IterationSK, i.[IterationPath]
	  FROM DimIteration i
	INNER JOIN @SelectedIterationList si ON i.[IterationPath] LIKE si.[IterationPath] + '\%'
;

DECLARE @IterationsAndChildren TABLE (__ID int, [Iteration Path] nvarchar(4000) null);
INSERT @IterationsAndChildren

	SELECT IterationSk, [IterationPath] FROM @SelectedIterationList
	UNION
	SELECT IterationSk, [IterationPath] FROM @ChildIterations
;


SELECT	a.Name AS CheckedInByName, 
	SUM(LinesAdded) AS LinesAdded, 
	SUM(LinesModified) As LinesModified, 
	SUM(LinesDeleted) AS LinesDeleted, 
	SUM(NetLinesAdded) AS NetLinesAdded
FROM (
	SELECT	System_Id, System_WorkItemType, cwv.WorkItemSK, fwico.ChangesetSK, dc.CheckedInBySK, Name, LinesAdded, LinesDeleted, LinesModified, NetLinesAdded
	FROM CurrentWorkItemView cwv
	INNER JOIN vFactWorkItemChangesetOverlay fwico ON cwv.WorkItemSK = fwico.WorkItemSK
	INNER JOIN DimChangeset dc ON fwico.ChangeSetSK = dc.ChangesetSK
	INNER JOIN DimPerson dp ON dc.CheckedInBySK = dp.PersonSK
	INNER JOIN FactCodeChurn fcc ON fwico.ChangeSetSK = fcc.ChangesetSK
	WHERE cwv.ProjectNodeGUID = @ProjectGuid  
	AND [AreaPath] IN (SELECT [AreaPath] FROM @AreasAndChildren) 
	AND [IterationPath] IN (SELECT [Iteration Path] FROM @IterationsAndChildren)
	) a
GROUP BY Name
ORDER BY Name DESC